package com.yjz.util.excel;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.context.i18n.LocaleContextHolder;
import org.springframework.util.Base64Utils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.text.DecimalFormat;
import java.util.*;

/**
 * @author tlfu
 * @date 2015年8月4日
 */
public class ExcelUtils {

  private final static Logger log = LoggerFactory.getLogger(ExcelUtils.class);
  private static final String XLSX = "xlsx";
  private static final String XLS = "xls";

  public static void exportWebExcelSheetsByConfig(HttpServletResponse httpServletResponse,
                                                  List<Map<String, Object>> data, String fileName) {
    try {
      setUpExcelHeader(httpServletResponse, fileName);
      excelExportSheetsByConfig(data, httpServletResponse.getOutputStream());
    } catch (IOException e) {
      log.error("exportWebExcelSheetsByConfig error:{}", e.getMessage());
    }
  }

  public static void exportWebExcelByConfig(HttpServletResponse httpServletResponse, ExcelConfig config,
                                            List<? extends Object> list, String fileName) {
    try {
      setUpExcelHeader(httpServletResponse, fileName);
      excelExportByConfig(list, httpServletResponse.getOutputStream(), config);
    } catch (IOException e) {
      log.error("exportWebExcelByConfig error:{}", e.getMessage());
    }
  }

  /**
   * web导出excel object是list集合时导出单sheet，是map时导出多sheet
   *
   * @param httpServletResponse
   * @param object
   * @param fileName
   */
  public static void exportWebExcel(HttpServletResponse httpServletResponse, Object object, String fileName) {
    try {
      setUpExcelHeader(httpServletResponse, fileName);
      if (object instanceof List) {
        excelExport((List) object, httpServletResponse.getOutputStream());
      } else if (object instanceof Map) {
        excelExport((Map) object, httpServletResponse.getOutputStream());
      }

    } catch (IOException e) {
      log.error("exportWebExcel error:{}", e.getMessage());
    }
  }


  private static void setUpExcelHeader(HttpServletResponse httpServletResponse, String fileName) throws UnsupportedEncodingException {
    httpServletResponse.setCharacterEncoding("UTF-8");
    httpServletResponse.setContentType("application/vnd.ms-excel");
    httpServletResponse.setHeader("content-disposition", "attachment; filename=" + URLEncoder.encode(fileName, "utf-8"));
  }

  public static void excelExportSheetsByConfig(List<Map<String, Object>> list, OutputStream out) {
    Workbook workbook = new XSSFWorkbook();
    for (Map<String, Object> map : list) {
      ExcelConfig config = (ExcelConfig) map.get("config");
      List data = (List) map.get("data");
      write2Excel(data, workbook, config);
    }
    produceExcel(workbook, out);
  }


  public static void excelExportByConfig(List<? extends Object> list, OutputStream out, ExcelConfig config) {
    Workbook workbook = new XSSFWorkbook();
    write2Excel(list, workbook, config);
    produceExcel(workbook, out);
  }

  /**
   * 导出excel,多sheet
   * 默认导出为格式为xlsx
   *
   * @param map key:sheetName,Object List集合
   *            当object是ExcelPicSheet输出图片sheet
   * @param out 输出流
   */
  public static void excelExport(Map<String, Object> map, OutputStream out) {
    Workbook workbook = new XSSFWorkbook();
    map.forEach((k, v) -> {
      List<? extends Object> list = (List<? extends Object>) v;
      if (!CollectionUtils.isEmpty(list)) {
        ExcelConfig config = new ExcelConfig();
        config.setSheetName(k);
        write2Excel(list, workbook, config);
      }
    });
    produceExcel(workbook, out);
  }

  private static void writePic(Workbook workbook, Sheet sheet, List<? extends Object> excelPicSheets) {
    for (Object object : excelPicSheets) {
      ExcelPicSheet excelPicSheet = (ExcelPicSheet) object;
      if (excelPicSheet.getType() == 1) {
        dealWithBase64Pic(workbook, sheet, excelPicSheet);
      }
    }

  }

  private static void dealWithBase64Pic(Workbook workbook, Sheet sheet, ExcelPicSheet excelPicSheet) {
    byte[] bs = Base64Utils.decodeFromString(excelPicSheet.getContent().split("base64,")[1]);
    Drawing patriarch = sheet.createDrawingPatriarch();
    XSSFClientAnchor anchor = new XSSFClientAnchor(excelPicSheet.getDx1(), excelPicSheet.getDy1(),
      excelPicSheet.getDx2(), excelPicSheet.getDy2(), excelPicSheet.getCol1(), excelPicSheet.getRow1(),
      excelPicSheet.getCol2(), excelPicSheet.getRow2());
    int picIndex = workbook.addPicture(bs, Workbook.PICTURE_TYPE_PNG);
    patriarch.createPicture(anchor, picIndex);
  }

  /**
   * 导出单sheet excel，默认sheet名为sheet1,默认格式xlsx
   *
   * @param list 内容对象集合
   * @param out  输出流
   */
  public static void excelExport(List<? extends Object> list, OutputStream out) {
    excelExport(list, null, out);
  }

  /**
   * 导出单sheet excel,默认格式xlsx
   *
   * @param list      内容对象集合
   * @param sheetName sheet名称
   * @param out       输出流
   */
  public static void excelExport(List<? extends Object> list, String sheetName, OutputStream out) {
    if (null == sheetName || "".equals(sheetName)) {
      sheetName = "sheet1";
    }
    ExcelConfig config = new ExcelConfig();
    config.setSheetName(sheetName);
    Workbook workbook = new XSSFWorkbook();
    write2Excel(list, workbook, config);
    produceExcel(workbook, out);
  }

  /**
   * 单sheet导入
   *
   * @param cls       excel row所对应的类
   * @param file      输入流
   * @param sheetName sheet名称
   */
  public static <T> List<T> excelImport(Class<T> cls, InputStream file, String sheetName) {
    return excelImport(cls, getWorkbook(file, XLSX), sheetName);
  }

  /**
   * 单sheet导入
   *
   * @param cls       excel row所对应的类
   * @param file      输入流
   * @param sheetName sheet名称
   * @param type      xlsx,xls
   */
  public static <T> List<T> excelImport(Class<T> cls, InputStream file, String sheetName, String type) {
    return excelImport(cls, getWorkbook(file, type), sheetName);
  }

  /**
   * 单sheet导入
   *
   * @param cls       excel row所对应的类
   * @param file      输入流
   * @param sheetName sheet名称
   */
  public static <T> List<T> excelImport(Class<T> cls, File file, String sheetName) throws Exception {
    return excelImport(cls, getWorkbook(new FileInputStream(file), file.getName()), sheetName);
  }

  /**
   * excel文件导入，多sheet
   *
   * @param map  key:sheetName,class：excel row所对应的类
   * @param file 输入流
   * @param type xlsx,xls
   */
  public static Map<String, List> excelImport(Map<String, Class> map, InputStream file, String type) {
    Map<String, List> returnMap = new HashMap<>(16);
    map.forEach((k, v) -> returnMap.put(k, excelImport(v, file, k, type)));
    return returnMap;
  }

  /**
   * 有时会有excel要加解密，可以先用此对象操作后再执行读写流程
   *
   * @param input 输入流
   * @param type  xlsx,xls
   */
  public static Workbook getWorkbook(InputStream input, String type) {
    try {
      Workbook wb;
      if (type.endsWith(XLSX)) {
        wb = new XSSFWorkbook(input);
      } else {
        wb = new HSSFWorkbook(input);
      }
      return wb;
    } catch (Exception e) {
      log.error(e.getMessage());
    }
    return null;
  }


  /**
   * @param cls       要导入的excel行所对应的类
   * @param wb        wb
   * @param sheetName sheet名称
   */
  public static <T> List<T> excelImport(Class<T> cls, Workbook wb, String sheetName) {
    List<T> list = new ArrayList<>();
    Sheet sheet;
    if (sheetName != null) {
      sheet = wb.getSheet(sheetName);
    } else {
      sheet = wb.getSheetAt(0);
    }
    int rows = sheet.getLastRowNum();
    try {
      Map<Integer, Field> mapping = getIdFieldMapping(cls.newInstance());
      for (int i = 1; i <= rows; i++) {
        Row row = sheet.getRow(i);
        T t = cls.newInstance();
        readCellValueWrite2Obj(row, t, mapping);
        list.add(t);
      }
    } catch (Exception e) {
      log.error(e.getMessage());
    }
    return list;
  }

  private static void write2Excel(List<? extends Object> list, Workbook workbook, ExcelConfig config) {
    Sheet sheet = workbook.createSheet(config.getSheetName());
    if (config.getStylerClass() == null) {
      config.setStylerClass(DefaultExcelExportStyler.class);
    }
    try {
      config.setStyler((IExcelExportStyler) config.getStylerClass()
        .getConstructor(Workbook.class).newInstance(workbook));
    } catch (Exception e) {
      log.error("write2Excel error:{}", e.getMessage());
    }
    Object o = list.get(0);
    if (o instanceof ExcelPicSheet) {
      writePic(workbook, sheet, list);
    } else {
      setExcelHeads(o, sheet, config);
      setExcelContent(list, sheet, config);
    }
  }

  private static void setExcelContent(List<? extends Object> list, Sheet sheet, ExcelConfig config) {
    int rowIndex = 1;
    if (config != null && !StringUtils.isEmpty(config.getTitle())) {
      rowIndex = 2;
    }
    for (Object entity : list) {
      Class cls = entity.getClass();
      Field[] fields = cls.getDeclaredFields();
      Row row = sheet.createRow(rowIndex);
      List<Field> fieldList = new ArrayList<>();
      if (fields != null && fields.length > 0) {
        for (Field f : fields) {
          if (getAnnotation(f).isShow()) {
            fieldList.add(f);
          }
        }
      }
      fieldList.sort((o1, o2) -> {
        ExcelAnnotation excel = getAnnotation(o1);
        ExcelAnnotation excel2 = getAnnotation(o2);
        return excel.id() > excel2.id() ? 1 : -1;
      });
      setCellValue(fieldList, row, entity, config);
      rowIndex++;
    }
  }

  private static void setCellValue(List<Field> fieldList, Row row, Object entity, ExcelConfig config) {
    try {
      for (int i = 0; i < fieldList.size(); i++) {
        Field f = fieldList.get(i);
        Cell cell = row.createCell(i);
        if (config.getStyler() != null) {
          cell.setCellStyle(config.getStyler().getCellStyle());
        }
        f.setAccessible(true);
        String value = (String) f.get(entity);
        f.setAccessible(false);
        if (value == null) {
          value = "";
        }
        cell.setCellValue(value);
      }
    } catch (Exception e) {
      log.error(e.getMessage());
    }
  }

  private static ExcelAnnotation getAnnotation(Field f) {
    f.setAccessible(true);
    ExcelAnnotation annotation = f.getAnnotation(ExcelAnnotation.class);
    f.setAccessible(false);
    return annotation;
  }

  private static void setExcelHeads(Object entity, Sheet sheet, ExcelConfig config) {
    List<ExcelAnnotation> excels = new ArrayList<>();
    Class cls = entity.getClass();
    Field[] fields = cls.getDeclaredFields();
    if (fields != null && fields.length > 0) {
      for (Field f : fields) {
        ExcelAnnotation excel = getAnnotation(f);
        if (excel.isShow()) {
          excels.add(excel);
        }
      }
    }
    excels.sort((e1, e2) -> e1.id() > e2.id() ? 1 : -1);

    int rowNum = 0;
    if (config != null && !StringUtils.isEmpty(config.getTitle())) {
      rowNum = buildTitle(sheet, config, excels.size());
    }

    buildHeader(excels, sheet, rowNum, config);
  }

  private static int buildTitle(Sheet sheet, ExcelConfig config, int fieldWidth) {
    Row rowTitle = sheet.createRow(0);
    rowTitle.setHeightInPoints(config.getTitleHeight());
    Cell cell = rowTitle.createCell(0);
    if (config.getStyler() != null) {
      cell.setCellStyle(config.getStyler().getTitleStyle());
    }
    RichTextString text = new XSSFRichTextString(config.getTitle());
    cell.setCellValue(text);
    addMergedRegion(sheet, 0, 0, 0, fieldWidth - 1);
    return 1;
  }

  private static void buildHeader(List<ExcelAnnotation> excels, Sheet sheet, int rowNum, ExcelConfig config) {
    Row rowHeader = sheet.createRow(rowNum);
    rowHeader.setHeightInPoints(config.getHeaderHeight());
    for (int i = 0; i < excels.size(); i++) {
      Cell cell = rowHeader.createCell(i);
      if (config.getStyler() != null) {
        cell.setCellStyle(config.getStyler().getHeaderStyle());
      } else {
        cell.setCellStyle(buildHeaderCellStyle(sheet.getWorkbook()));
      }
      String name = excels.get(i).name();
      if (LocaleContextHolder.getLocale().equals(Locale.US)) {
        name = excels.get(i).enName();
      }
      RichTextString text = new XSSFRichTextString(name);
      cell.setCellValue(text);
      sheet.setColumnWidth((short) i, 256 * excels.get(i).width());
    }
  }

  private static XSSFCellStyle buildHeaderCellStyle(Workbook workbook) {
    XSSFCellStyle style = ((XSSFWorkbook) workbook).createCellStyle();
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
    return style;
  }

  private static void produceExcel(Workbook workbook, OutputStream out) {
    try {
      workbook.write(out);
    } catch (IOException e) {
      log.error(e.getMessage());
    } finally {
      try {
        if (workbook != null) {
          workbook.close();
        }
      } catch (IOException e2) {
        log.warn(e2.getMessage());
      }
    }
  }

  private static void readCellValueWrite2Obj(Row row, Object t, Map<Integer, Field> map) throws Exception {
    int cols = row.getLastCellNum();
    for (int j = 0; j < cols; j++) {
      Cell cell = row.getCell(j);
      String value = null;
      Object cellValue = null;
      if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
        DecimalFormat df = new DecimalFormat("0");
        cellValue = df.format(cell.getNumericCellValue());
      } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
        cellValue = cell.getStringCellValue();
      } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN) {
        cellValue = cell.getBooleanCellValue();
      } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_FORMULA) {
        cellValue = cell.getCellFormula();
      } else if (cell != null && cell.getCellType() == HSSFCell.CELL_TYPE_BLANK) {
        cellValue = "";
      }
      if (cellValue != null) {
        value = String.valueOf(cellValue);
      }
      setCellValue2Obj(t, j, value, map);
    }
  }

  private static Map<Integer, Field> getIdFieldMapping(Object entity) {
    Class cls = entity.getClass();
    Field[] fields = cls.getDeclaredFields();
    Map<Integer, Field> map = new HashMap<>(16);
    if (fields != null && fields.length > 0) {
      for (Field f : fields) {
        ExcelAnnotation annotation = getAnnotation(f);
        if (annotation.isShow()) {
          if (map.containsKey(annotation.id())) {
            throw new IllegalArgumentException("实体类中 ExcelAnnotation存在重复的id:" + annotation.id());
          }
          map.put(annotation.id(), f);
        }
      }
    }
    return map;
  }

  private static void setCellValue2Obj(Object t, int currentColumn, String value, Map<Integer, Field> map)
    throws Exception {
    Class cls = t.getClass();
    Field f = map.get(currentColumn);
    if (f == null) {
      return;
    }
    String name = f.getName();
    Method m = cls.getDeclaredMethod("set" + name.substring(0, 1).toUpperCase() + name.substring(1), String.class);
    m.setAccessible(true);
    m.invoke(t, value);
    m.setAccessible(false);
  }

  private static void addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol) {
    try {
      sheet.addMergedRegion(new CellRangeAddress(firstRow, lastRow, firstCol, lastCol));
    } catch (Exception e) {
      log.error("发生了一次合并单元格错误,{},{},{},{}", new Integer[]{
        firstRow, lastRow, firstCol, lastCol
      });
    }
  }
}